<?php 
$req_path="./";
include_once($req_path."include/ini.php");
require_once($req_path."include/db.php");

class M_cancelbill extends DB{
	private $dbConn;
	private $rowCount="0";
	private $ID="0";
	private $sqlSelect =" select * from tb_pos where 1=1 and IsCancel=0 ";
	
	function M_cancelbill(){
		parent::__construct();
	}
	function getListPage(){
		global $list_page;
		return $list_page;
	}

	function getData( $branch=0,$billno=null,$datefrom=null,$dateto=null,$name=null ,$pageStart=1,$type=0 ){
		global  $list_page;
		$sqlscript= "select pos.ID , pos.CustID , Case When pos.CustID=0 then 'Non-member' else CONCAT(c.FirstName	,' ' ,c.LastName) end as Name "
					." ,DATE_FORMAT( pos.SaleDate , '%d/%m/%Y' ) SaleDate1 "
					." , b.BranchCode  , DATE_FORMAT( pos.SaleDate , '%y%m%d' )  as BillNo " 
					." , b.BranchName ,IsCancel "
					//." , Case when pos.PaymentType=1 then 'Cash' when pos.PaymentType=2 then 'Credit Card' Else 'Smart Purse' End as PaymentType "
					." , PaymentAmount , Amount , Discount, TotalNet as Total , SaleBy "
					." from tb_pos pos "
					." left join tb_customer c on pos.CustID=c.ID "
					." left join tb_branch b on b.ID=pos.Branch "
					." where 1=1 and IsCancel='".$type."'";
		
		if (isset($name) && $name!=""){
			$sqlscript.= " and (c.FirstName like '%". $name."%' or c.LastName like '%". $name ."')";
		}
		if (isset($billno) && $billno!=""){
			$sqlscript.= " and pos.ID =". $billno;
		}
		if (isset($branch) && $branch!="0"){
			$sqlscript.= " and b.ID =". $branch;
		}		
		if (isset($datefrom) && $datefrom!=""){
			list($day, $month, $year) = split('/', $datefrom);
			$bfrom= $year."/".$month."/".$day;
			
			list($day, $month, $year) = split('/', $dateto);
			$bTo=$year."/".$month."/".$day;
			
			$sqlscript.= " and DATE_FORMAT( pos.SaleDate , '%Y/%m/%d' ) Between'". $bfrom."'  and '".$bTo."'";
		}
		// return $sqlscript;
		//Order 
		$sqlscript.= " order by SaleDate desc " ;
			//	return $sqlscript;
		//Execute
		$result = $this->ExecuteData($sqlscript);
		//Get all rows
		$rows =$result->RecordCount();
	
		//Page limit
		$sqlscript.= " limit ". ($pageStart-1)*$list_page. ", " . $list_page;
		//paging
		$result = $this->ExecuteData($sqlscript);	
	
		$totalpage=1;
		if ($rows>0){
			$rt = $rows%$list_page;
			
			if($rt!=0) {
				$totalpage = floor($rows/$list_page)+1;
			}
			else {
				$totalpage = floor($rows/$list_page);
			}
		}
		
		$this->rowCount=$rows;
		$this->totalpage=$totalpage;	
		
		return $result;	 
	}
	// Get Header
	function getHeaderData( $branch=0,$billno=null  ){
 
		$sqlscript= "select pos.ID , pos.CustID , Case When pos.CustID=0 then 'Non-member' else CONCAT(c.FirstName	,' ' ,c.LastName) end as Name "
					." ,DATE_FORMAT( pos.SaleDate , '%d/%m/%Y' ) SaleDate1 "
					." , b.BranchCode  , DATE_FORMAT( pos.SaleDate , '%y%m%d' )  as BillNo " 
					." , b.BranchName ,IsCancel "
					//." , Case when pos.PaymentType=1 then 'Cash' when pos.PaymentType=2 then 'Credit Card' Else 'Smart Purse' End as PaymentType "
					." , PaymentAmount , Amount , Discount, TotalNet as Total , SaleBy "
					." from tb_pos pos "
					." left join tb_customer c on pos.CustID=c.ID "
					." left join tb_branch b on b.ID=pos.Branch "
					." where 1=1  ";
		
 
		if (isset($billno) && $billno!=""){
			$sqlscript.= " and pos.ID =". $billno;
		}
		if (isset($branch) && $branch!="0"){
			$sqlscript.= " and b.ID =". $branch;
		}		
	 
		// return $sqlscript;
		//Order 
		$sqlscript.= " order by SaleDate desc " ;
			//	return $sqlscript;
		//Execute
		$result = $this->ExecuteData($sqlscript);
		//Get all rows
		$rows =$result->RecordCount();
  		
		return $result;	 
	}
	// END Get header
	
	function getDataDetail($id){
		$sql=" Select d.id, p.ProductName , c.CatName ,t.Type as ProductType "
			." ,d.Amount, d.Price, d.Total - d.Discount as TotalAmt , d.Discount , d.Total , u.Unit "
			." , d.Product as Pid , p.Type as PTypeId"
			." From tb_pos_detail d"
			." left join tb_product p on p.ID=d.Product"
			." left join tb_product_category c on c.CatID=p.Category "
			." left join tb_product_type t on t.ID=p.Type "
			." left join tb_units u on u.ID=p.MainUnit "
			." where   d.Pos_id=".$id;
			
		$sql.= " order by ProductName asc " ;
		//return $sql;
		//Execute
		$result = $this->ExecuteData($sql);
		//Get all rows
		$rows =$result->RecordCount();		
		
		$this->rowCount=$rows;
		$this->totalpage=$totalpage;	
		
		return $result;
			
	}
	function checkAlreadyUsed($id){
		$sql=" SELECT *
				FROM tb_pos p
				Join tb_customer_service c on c.POSID=p.ID
				where p.ID=".$id." and c.Times > c.Remain";
		$result=$this->ExecuteData($sql);
		
		if ($result->RecordCount() > 0){
			return true;// used  
		}else{
			return false;// not used 
		}
		
	}
	function cancelBill_Main($id,$user){
		//Update POS
	    $sql= "update tb_pos set IsCancel=1 , UpdatedDate=Now(), UpdatedBy='" .$user."' Where ID=".$id;
		$this->ExecuteData($sql);
		
		//Update credit sale
		$sql="update tb_creditsale set IsDeleted = 1 , UpdatedDate=Now(), UpdatedBy='" .$user."' Where PosID=".$id;
		$this->ExecuteData($sql);
		
		//Update customer_service
		$sql="update tb_customer_service set IsCancel = 1  Where POSID=".$id;
		$this->ExecuteData($sql);
	}
	function getPaymentHistory($posid=0){
		$sql=" select CASE WHEN p.PaymentType=1 THEN 'Cash' When p.PaymentType=2 THEN 'Credit Card' Else 'Smart Purse' END PayType ,   "
			." Sum(p.PaymentAmount) PaymentAmount "
			." from tb_pos po  "
			. " join tb_pos_payment p on po.ID=p.Pos_id "
			. " where 1=1 "
			. " and po.ID=".$posid
			. " group by p.PaymentType ";
		$result = $this->ExecuteData($sql);	
		$arr = array();
		while ($row = $result->FetchRow()){
			array_push($arr,$row);
		}
		return  $arr ;
	}	
	function getRowCount(){
		return $this->rowCount;
	}
	
	function getLastInsert(){
		return $this->ID;
	}
	function cancelBill_detail_all($id){
		$sql = "update tb_pos_detail set IsCancel=1 where Pos_id=".$id;
		$this->ExecuteData($sql);
	}
	function cancelBill_detail($id){
		$sql = "update tb_pos_detail set IsCancel=1 where id in (".$id.")";
		$this->ExecuteData($sql);
	}
	function updateBill($id){
		$sql="select sum(Amount*Price) Amt,sum(Discount) Discount,sum(Total) Total from tb_pos_detail where IsCancel=0 and  Pos_id=".$id;
		$result=$this->ExecuteData($sql);
		$rows=$result->FetchRow();
		
		$sqlupdate="update tb_pos set Amount=".$rows["Amt"].
					", Discount=".$rows["Discount"].
					", RemainAmount=".$rows["Amt"]."-".$rows["Discount"]." - PaymentAmount".
					" where ID=".$id;
		//return 
		$this->ExecuteData($sqlupdate);
		return $sqlupdate;
	}
	function InsertData($val){
		if (isset($val)){
			$sqlInsert=$this->InsertSqlScript($this->sqlSelect, $val);
			$this->ExecuteData($sqlInsert);
			$this->ID = $this->getLastID();
			return $this->ID;
		}
	}
	function updateData($sql, $val){
		if(isset($val)){
			$sqlUpdate=$this->UpdateSqlScript($sql,$val);
			return $this->ExecuteData($sqlUpdate);
		}
	}
	function getTotalPage(){
		return $this->totalpage;
	}

}
?>